using MySql.Data.MySqlClient;
using WpfApp1.Models;

namespace WpfApp1.DataAccess;

public class TeacherCourseSelectDataAccess : ITeacherCourseSelectDataAccess
{
    //查看选择自己任课课程的学生信息（教师）
    public List<StudentCourseDto> GetStudentCourses(string teacherNumber)
    {
        var courses = new List<StudentCourseDto>();
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = @"SELECT cs.student_number, s.name, c.course_number, 
                       c.course_name, c.credits, c.hours, cs.grades
                       FROM courses c
                       JOIN course_selection cs ON c.course_number = cs.course_number
                       JOIN students s ON cs.student_number = s.student_number
                       WHERE c.teacher_number = @teacherNumber";
            
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@teacherNumber", teacherNumber);
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    courses.Add(new StudentCourseDto(
                        reader.GetString("course_number"),
                        reader.GetString("student_number"),
                        reader.GetString("course_name"),
                        reader.GetInt32("credits"),
                        reader.GetInt32("hours"),
                        reader.GetString("name"),
                        reader.GetInt32("grades")
                    ));
                }
            }
        }
        return courses;
    }
}